import json

import requests
from flask import Flask, jsonify, render_template
from flask_migrate import Migrate
from sklearn.model_selection import train_test_split
from sklearn.tree import DecisionTreeClassifier, export_graphviz
import random

from exts import db, DaviesBouldin
import pandas as pd
import numpy as np
from sklearn.datasets import load_boston
import statsmodels.formula.api as smf

from models import C1, C2, C3, C2_2, C1_1
import config

app = Flask(__name__)
app.config.from_object(config)
db.init_app(app=app)

migrate = Migrate(app, db)


# todo set_1 Excel表1转存mysql
@app.route("/set_1")
def set_1():
    return "error"
    df = pd.read_excel('c1.xlsx')
    t = np.array(df)
    for i in t:
        c1 = C1(
            cultural_relic_number=str(i[0]),
            ornamentation=str(i[1]),
            type=str(i[2]),
            color="数据缺失" if str(i[3]) == "nan" else str(i[3]),
            surface_weathering=str(i[4])
        )

        db.session.add(c1)
        db.session.commit()

    return "success"


# todo set_2 Excel表2转存mysql
@app.route("/set_2")
def set_2():
    return "error"
    df = pd.read_excel('c2.xlsx')
    t = np.array(df)
    for i in t:
        c2 = C2(
            cultural_relics_sampling_point=str(i[0]),
            SiO2=0 if str(i[1]) == "nan" else str(i[1]),
            Na2O=0 if str(i[2]) == "nan" else str(i[2]),
            K2O=0 if str(i[3]) == "nan" else str(i[3]),
            CaO=0 if str(i[4]) == "nan" else str(i[4]),
            MgO=0 if str(i[5]) == "nan" else str(i[5]),
            Al2O3=0 if str(i[6]) == "nan" else str(i[6]),
            Fe2O3=0 if str(i[7]) == "nan" else str(i[7]),
            CuO=0 if str(i[8]) == "nan" else str(i[8]),
            PbO=0 if str(i[9]) == "nan" else str(i[9]),
            BaO=0 if str(i[10]) == "nan" else str(i[10]),
            P2O5=0 if str(i[11]) == "nan" else str(i[11]),
            SrO=0 if str(i[12]) == "nan" else str(i[12]),
            SnO2=0 if str(i[13]) == "nan" else str(i[13]),
            SO2=0 if str(i[14]) == "nan" else str(i[14]),
        )

        db.session.add(c2)
        db.session.commit()

    return "success"


# todo set_3 Excel表3转存mysql
@app.route("/set_3")
def set_3():
    return "error"
    df = pd.read_excel('c3.xlsx')
    t = np.array(df)
    for i in t:
        c3 = C3(
            cultural_relic_number=str(i[0]),
            surface_weathering=str(i[1]),
            SiO2=0 if str(i[2]) == "nan" else str(i[2]),
            Na2O=0 if str(i[3]) == "nan" else str(i[3]),
            K2O=0 if str(i[4]) == "nan" else str(i[4]),
            CaO=0 if str(i[5]) == "nan" else str(i[5]),
            MgO=0 if str(i[6]) == "nan" else str(i[6]),
            Al2O3=0 if str(i[7]) == "nan" else str(i[7]),
            Fe2O3=0 if str(i[8]) == "nan" else str(i[8]),
            CuO=0 if str(i[9]) == "nan" else str(i[9]),
            PbO=0 if str(i[10]) == "nan" else str(i[10]),
            BaO=0 if str(i[11]) == "nan" else str(i[11]),
            P2O5=0 if str(i[12]) == "nan" else str(i[12]),
            SrO=0 if str(i[13]) == "nan" else str(i[13]),
            SnO2=0 if str(i[14]) == "nan" else str(i[14]),
            SO2=0 if str(i[15]) == "nan" else str(i[15])
        )

        db.session.add(c3)
        db.session.commit()

    return "success"


# todo get_1
@app.route("/get_1")
def get_1():
    l = []
    for i in db.session.query(C1).all():
        l.append({
            "cultural_relic_number": i.cultural_relic_number,
            "ornamentation": i.ornamentation,
            "type": i.type,
            "color": i.color,
            "surface_weathering": i.surface_weathering
        })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": l})


# todo get_1_1
@app.route("/get_1_1")
def get_1_1():
    l = []
    ornamentation = {"all": {
        "wind": 0,
        "no_wind": 0,
        "data": []
    }}
    color = {"all": {
        "wind": 0,
        "no_wind": 0,
        "data": []
    }}
    type = {"all": {
        "wind": 0,
        "no_wind": 0,
        "data": []
    }}

    for i in db.session.query(C1).all():
        if i.color == "数据缺失":
            continue

        if i.color not in color:
            color[i.color] = {
                "wind": 0,
                "no_wind": 0,
                "data": []
            }
        if i.ornamentation not in ornamentation:
            ornamentation[i.ornamentation] = {
                "wind": 0,
                "no_wind": 0,
                "data": []
            }
        if i.type not in type:
            type[i.type] = {
                "wind": 0,
                "no_wind": 0,
                "data": []
            }

        if i.surface_weathering == "风化":
            color[i.color]["wind"] += 1
            color["all"]["wind"] += 1
            ornamentation[i.ornamentation]["wind"] += 1
            ornamentation["all"]["wind"] += 1
            type[i.type]["wind"] += 1
            type["all"]["wind"] += 1
        else:
            color[i.color]["no_wind"] += 1
            color["all"]["no_wind"] += 1
            ornamentation[i.ornamentation]["no_wind"] += 1
            ornamentation["all"]["no_wind"] += 1
            type[i.type]["no_wind"] += 1
            type["all"]["no_wind"] += 1
    y1 = 0
    for i in ornamentation:
        x1 = sum([ornamentation[j]["no_wind"] if j == i else 0 for j in ornamentation])
        x2 = sum([ornamentation[j]["wind"] if j == i else 0 for j in ornamentation])
        x3 = sum([ornamentation[j]["no_wind"] if j == i else 0 for j in ornamentation]) + sum(
            [ornamentation[j]["wind"] if j == i else 0 for j in ornamentation])
        y1 = y1 + ((24 / 54) * x3 - x1) * ((24 / 54) * x3 - x1) / ((24 / 54) * x3) + ((30 / 54) * x3 - x2) * (
                (30 / 54) * x3 - x2) / ((30 / 54) * x3)
        l.append({
            "title": "纹饰",
            "name": i,
            "no_wind": x1,
            "wind": x2,
            "sum": x3,
            "x2": y1 if i == "B" else "",
            "standard_x2": y1 if i == "B" else "",
            "p": 0.056 if i == "B" else ""
        })
    y2 = 0
    for i in color:
        x1 = sum([color[j]["no_wind"] if j == i else 0 for j in color])
        x2 = sum([color[j]["wind"] if j == i else 0 for j in color])
        x3 = sum([color[j]["no_wind"] if j == i else 0 for j in color]) + sum(
            [color[j]["wind"] if j == i else 0 for j in color])
        y2 = y2 + ((24 / 54) * x3 - x1) * ((24 / 54) * x3 - x1) / ((24 / 54) * x3) + ((30 / 54) * x3 - x2) * (
                (30 / 54) * x3 - x2) / ((30 / 54) * x3)
        l.append({
            "title": "颜色",
            "name": i,
            "no_wind": x1,
            "wind": x2,
            "sum": x3,
            "x2": y2 if i == "绿" else "",
            "standard_x2": y2 if i == "绿" else "",
            "p": 0.507 if i == "绿" else ""
        })
    y3 = 0
    stand = []
    standy3 = 0
    for i in type:
        x1 = sum([type[j]["no_wind"] if j == i else 0 for j in type])
        x2 = sum([type[j]["wind"] if j == i else 0 for j in type])
        x3 = sum([type[j]["no_wind"] if j == i else 0 for j in type]) + sum(
            [type[j]["wind"] if j == i else 0 for j in type])

        y3 = y3 + ((24 / 54) * x3 - x1) * ((24 / 54) * x3 - x1) / ((24 / 54) * x3) + ((30 / 54) * x3 - x2) * (
                (30 / 54) * x3 - x2) / ((30 / 54) * x3)
        stand.append(x1)
        stand.append(x2)
        if (i == '铅钡'):
            standy3 = (abs(stand[2] * stand[5] - stand[3] * stand[4]) - 27) * (
                    abs(stand[2] * stand[5] - stand[3] * stand[4]) - 27) * 54 / (stand[2] + stand[3]) / (
                              stand[4] + stand[5]) / (stand[2] + stand[4]) / (stand[5] + stand[3])
        l.append({
            "title": "类型",
            "name": i,
            "no_wind": x1,
            "wind": x2,
            "sum": x3,
            "x2": y3 if i == "铅钡" else "",
            "standard_x2": standy3 if i == "铅钡" else "",
            "p": 0.020 if i == "铅钡" else ""
        })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": l})


# todo get_1_2
@app.route("/get_1_2")
def get_1_2():
    l = []

    for i in db.session.query(C2).all():
        c1 = db.session.query(C1).filter(C1.cultural_relic_number == i.cultural_relics_sampling_point[0:2]).first()
        l.append({
            "cultural_relic_number": c1.cultural_relic_number,
            "ornamentation": c1.ornamentation,
            "type": c1.type,
            "color": c1.color,
            "surface_weathering": c1.surface_weathering,
            "cultural_relics_sampling_point": i.cultural_relics_sampling_point,
            "SiO2": i.SiO2,
            "Na2O": i.Na2O,
            "K2O": i.K2O,
            "CaO": i.CaO,
            "MgO": i.MgO,
            "Al2O3": i.Al2O3,
            "Fe2O3": i.Fe2O3,
            "CuO": i.CuO,
            "PbO": i.PbO,
            "BaO": i.BaO,
            "P2O5": i.P2O5,
            "SrO": i.SrO,
            "SnO2": i.SnO2,
            "SO2": i.SO2,
        })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": l})


# todo get_1_2_0
@app.route("/get_1_2_0")
def get_1_2_0():
    l = []

    for i in db.session.query(C2).all():
        l.append({
            "cultural_relic_number": i.c1.cultural_relic_number,
            "ornamentation": i.c1.ornamentation,
            "type": i.c1.type,
            "color": i.c1.color,
            "surface_weathering": i.c1.surface_weathering,
            "cultural_relics_sampling_point": i.cultural_relics_sampling_point,
            "SiO2": i.SiO2,
            "Na2O": i.Na2O,
            # "K2O": i.K2O,
            "CaO": i.CaO,
            "MgO": i.MgO,
            "Al2O3": i.Al2O3,
            "Fe2O3": i.Fe2O3,
            "CuO": i.CuO,
            # "PbO": i.PbO,
            "BaO": i.BaO,
            "P2O5": i.P2O5,
            "SrO": i.SrO,
            "SnO2": i.SnO2,
            "SO2": i.SO2,
        })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": l})


# todo get_1_2_1 高钾
@app.route("/get_1_2_1")
def get_1_2_1():
    l = []

    for i in db.session.query(C2).all():
        c1 = db.session.query(C1).filter(C1.cultural_relic_number == i.cultural_relics_sampling_point[0:2]).first()
        if c1.type != "高钾":
            continue

        l.append({
            "cultural_relic_number": c1.cultural_relic_number,
            "ornamentation": c1.ornamentation,
            "type": c1.type,
            "color": c1.color,
            "surface_weathering": c1.surface_weathering,
            "cultural_relics_sampling_point": i.cultural_relics_sampling_point,
            "SiO2": i.SiO2,
            "Na2O": i.Na2O,
            "K2O": i.K2O,
            "CaO": i.CaO,
            "MgO": i.MgO,
            "Al2O3": i.Al2O3,
            "Fe2O3": i.Fe2O3,
            "CuO": i.CuO,
            "PbO": i.PbO,
            "BaO": i.BaO,
            "P2O5": i.P2O5,
            "SrO": i.SrO,
            "SnO2": i.SnO2,
            "SO2": i.SO2,
        })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": l})


# todo get_1_2_2 铅钡
@app.route("/get_1_2_2")
def get_1_2_2():
    l = []

    for i in db.session.query(C2).all():
        c1 = db.session.query(C1).filter(C1.cultural_relic_number == i.cultural_relics_sampling_point[0:2]).first()
        if c1.type != "铅钡":
            continue

        l.append({
            "cultural_relic_number": c1.cultural_relic_number,
            "ornamentation": c1.ornamentation,
            "type": c1.type,
            "color": c1.color,
            "surface_weathering": c1.surface_weathering,
            "cultural_relics_sampling_point": i.cultural_relics_sampling_point,
            "SiO2": i.SiO2,
            "Na2O": i.Na2O,
            "K2O": i.K2O,
            "CaO": i.CaO,
            "MgO": i.MgO,
            "Al2O3": i.Al2O3,
            "Fe2O3": i.Fe2O3,
            "CuO": i.CuO,
            "PbO": i.PbO,
            "BaO": i.BaO,
            "P2O5": i.P2O5,
            "SrO": i.SrO,
            "SnO2": i.SnO2,
            "SO2": i.SO2,
        })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": l})


# todo get_1_2_2_1 铅钡 无风化
@app.route("/get_1_2_2_1")
def get_1_2_2_1():
    l = []

    with open("static/json/get_1_3.json") as j:
        json_data = json.load(j)
    # print(json_data)
    data = json_data["data"]

    for i in data:
        if i['type'] != "铅钡" or i['surface_weathering'] != "无风化":
            continue

        l.append({
            "type": i['type'],
            "surface_weathering": i['surface_weathering'],
            "cultural_relics_sampling_point": i['cultural_relics_sampling_point'],
            "SiO2": i['SiO2'],
            "Na2O": i['Na2O'],
            "K2O": i['K2O'],
            "CaO": i['CaO'],
            "MgO": i['MgO'],
            "Al2O3": i['Al2O3'],
            "Fe2O3": i['Fe2O3'],
            "CuO": i['CuO'],
            "PbO": i['PbO'],
            "BaO": i['BaO'],
            "P2O5": i['P2O5'],
            "SrO": i['SrO'],
            "SnO2": i['SnO2'],
            "SO2": i['SO2'],
        })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": l})


# todo get_1_2_2_2 铅钡 风化
@app.route("/get_1_2_2_2")
def get_1_2_2_2():
    l = []

    with open("static/json/get_1_3.json") as j:
        json_data = json.load(j)
    # print(json_data)
    data = json_data["data"]

    for i in data:
        if i['type'] != "铅钡" or i['surface_weathering'] != "风化":
            continue

        l.append({
            "type": i['type'],
            "surface_weathering": i['surface_weathering'],
            "cultural_relics_sampling_point": i['cultural_relics_sampling_point'],
            "SiO2": i['SiO2'],
            "Na2O": i['Na2O'],
            "K2O": i['K2O'],
            "CaO": i['CaO'],
            "MgO": i['MgO'],
            "Al2O3": i['Al2O3'],
            "Fe2O3": i['Fe2O3'],
            "CuO": i['CuO'],
            "PbO": i['PbO'],
            "BaO": i['BaO'],
            "P2O5": i['P2O5'],
            "SrO": i['SrO'],
            "SnO2": i['SnO2'],
            "SO2": i['SO2'],
        })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": l})


# todo get_1_2_2_3 铅钡 严重风化
@app.route("/get_1_2_2_3")
def get_1_2_2_3():
    l = []

    with open("static/json/get_1_3.json") as j:
        json_data = json.load(j)
    # print(json_data)
    data = json_data["data"]

    for i in data:
        if i['type'] != "铅钡" or i['surface_weathering'] != "严重风化":
            continue

        l.append({
            "type": i['type'],
            "surface_weathering": i['surface_weathering'],
            "cultural_relics_sampling_point": i['cultural_relics_sampling_point'],
            "SiO2": i['SiO2'],
            "Na2O": i['Na2O'],
            "K2O": i['K2O'],
            "CaO": i['CaO'],
            "MgO": i['MgO'],
            "Al2O3": i['Al2O3'],
            "Fe2O3": i['Fe2O3'],
            "CuO": i['CuO'],
            "PbO": i['PbO'],
            "BaO": i['BaO'],
            "P2O5": i['P2O5'],
            "SrO": i['SrO'],
            "SnO2": i['SnO2'],
            "SO2": i['SO2'],
        })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": l})


# todo get_1_2_2_4 铅钡 将风化的通过补上均值恢复为未风化的化学元素
@app.route("/get_1_2_2_4")
def get_1_2_2_4():
    return_data = []

    # 严重风化数据
    with open("static/json/get_1_2_2_3.json") as j:
        json_data = json.load(j)
    data_hard_wind = json_data["data"]
    # 风化数据
    with open("static/json/get_1_2_2_2.json") as j:
        json_data = json.load(j)
    data_wind = json_data["data"]
    # 无风化数据
    with open("static/json/get_1_2_2_1.json") as j:
        json_data = json.load(j)
    data_no_wind = json_data["data"]
    # 均值修补数据
    with open("static/json/mean_difference.json") as j:
        json_data = json.load(j)
    data_mean_difference = json_data

    # 严重风化转风化再转无风化
    for i in data_hard_wind:
        i["SiO2"] = float(i["SiO2"]) + float(data_mean_difference["hard_wind_to_wind"]["SiO2"]) + float(
            data_mean_difference["wind_to_no_wind"]["SiO2"])
        i["Na2O"] = float(i["Na2O"]) + float(data_mean_difference["hard_wind_to_wind"]["Na2O"]) + float(
            data_mean_difference["wind_to_no_wind"]["Na2O"])
        i["Na2O"] = float(i["Na2O"]) + float(data_mean_difference["hard_wind_to_wind"]["Na2O"]) + float(
            data_mean_difference["wind_to_no_wind"]["Na2O"])
        i["K2O"] = float(i["K2O"]) + float(data_mean_difference["hard_wind_to_wind"]["K2O"]) + float(
            data_mean_difference["wind_to_no_wind"]["K2O"])
        i["CaO"] = float(i["CaO"]) + float(data_mean_difference["hard_wind_to_wind"]["CaO"]) + float(
            data_mean_difference["wind_to_no_wind"]["CaO"])
        i["MgO"] = float(i["MgO"]) + float(data_mean_difference["hard_wind_to_wind"]["MgO"]) + float(
            data_mean_difference["wind_to_no_wind"]["MgO"])
        i["Al2O3"] = float(i["Al2O3"]) + float(data_mean_difference["hard_wind_to_wind"]["Al2O3"]) + float(
            data_mean_difference["wind_to_no_wind"]["Al2O3"])
        i["Fe2O3"] = float(i["Fe2O3"]) + float(data_mean_difference["hard_wind_to_wind"]["Fe2O3"]) + float(
            data_mean_difference["wind_to_no_wind"]["Fe2O3"])
        i["CuO"] = float(i["CuO"]) + float(data_mean_difference["hard_wind_to_wind"]["CuO"]) + float(
            data_mean_difference["wind_to_no_wind"]["CuO"])
        i["PbO"] = float(i["PbO"]) + float(data_mean_difference["hard_wind_to_wind"]["PbO"]) + float(
            data_mean_difference["wind_to_no_wind"]["PbO"])
        i["BaO"] = float(i["BaO"]) + float(data_mean_difference["hard_wind_to_wind"]["BaO"]) + float(
            data_mean_difference["wind_to_no_wind"]["BaO"])
        i["P2O5"] = float(i["P2O5"]) + float(data_mean_difference["hard_wind_to_wind"]["P2O5"]) + float(
            data_mean_difference["wind_to_no_wind"]["P2O5"])
        i["SrO"] = float(i["SrO"]) + float(data_mean_difference["hard_wind_to_wind"]["SrO"]) + float(
            data_mean_difference["wind_to_no_wind"]["SrO"])
        i["SnO2"] = float(i["SnO2"]) + float(data_mean_difference["hard_wind_to_wind"]["SnO2"]) + float(
            data_mean_difference["wind_to_no_wind"]["SnO2"])
        i["SO2"] = float(i["SO2"]) + float(data_mean_difference["hard_wind_to_wind"]["SO2"]) + float(
            data_mean_difference["wind_to_no_wind"]["SO2"])

        return_data.append(i)

    # 风化转无风化
    for i in data_wind:
        i["SiO2"] = float(i["SiO2"]) + float(data_mean_difference["wind_to_no_wind"]["SiO2"])
        i["Na2O"] = float(i["Na2O"]) + float(data_mean_difference["wind_to_no_wind"]["Na2O"])
        i["Na2O"] = float(i["Na2O"]) + float(data_mean_difference["wind_to_no_wind"]["Na2O"])
        i["K2O"] = float(i["K2O"]) + float(data_mean_difference["wind_to_no_wind"]["K2O"])
        i["CaO"] = float(i["CaO"]) + float(data_mean_difference["wind_to_no_wind"]["CaO"])
        i["MgO"] = float(i["MgO"]) + float(data_mean_difference["wind_to_no_wind"]["MgO"])
        i["Al2O3"] = float(i["Al2O3"]) + float(data_mean_difference["wind_to_no_wind"]["Al2O3"])
        i["Fe2O3"] = float(i["Fe2O3"]) + float(data_mean_difference["wind_to_no_wind"]["Fe2O3"])
        i["CuO"] = float(i["CuO"]) + float(data_mean_difference["wind_to_no_wind"]["CuO"])
        i["PbO"] = float(i["PbO"]) + float(data_mean_difference["wind_to_no_wind"]["PbO"])
        i["BaO"] = float(i["BaO"]) + float(data_mean_difference["wind_to_no_wind"]["BaO"])
        i["P2O5"] = float(i["P2O5"]) + float(data_mean_difference["wind_to_no_wind"]["P2O5"])
        i["SrO"] = float(i["SrO"]) + float(data_mean_difference["wind_to_no_wind"]["SrO"])
        i["SnO2"] = float(i["SnO2"]) + float(data_mean_difference["wind_to_no_wind"]["SnO2"])
        i["SO2"] = float(i["SO2"]) + float(data_mean_difference["wind_to_no_wind"]["SO2"])

        return_data.append(i)

    for i in data_no_wind:
        return_data.append(i)

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": return_data})


def get_1_3_fun(cultural_relics_sampling_point):
    if "严重风化" in cultural_relics_sampling_point:
        return "严重风化"
    elif "未风化" in cultural_relics_sampling_point:
        return "无风化"
    else:
        return "error"


# todo get_1_3
@app.route("/get_1_3")
def get_1_3():
    l = []

    for i in db.session.query(C2).all():
        c1 = db.session.query(C1).filter(C1.cultural_relic_number == i.cultural_relics_sampling_point[0:2]).first()
        l.append({
            "type": c1.type,
            "surface_weathering": c1.surface_weathering if len(
                i.cultural_relics_sampling_point) == 2 or "部位" in i.cultural_relics_sampling_point else get_1_3_fun(
                i.cultural_relics_sampling_point),
            "cultural_relics_sampling_point": i.cultural_relics_sampling_point,
            "SiO2": i.SiO2,
            "Na2O": i.Na2O,
            "K2O": i.K2O,
            "CaO": i.CaO,
            "MgO": i.MgO,
            "Al2O3": i.Al2O3,
            "Fe2O3": i.Fe2O3,
            "CuO": i.CuO,
            "PbO": i.PbO,
            "BaO": i.BaO,
            "P2O5": i.P2O5,
            "SrO": i.SrO,
            "SnO2": i.SnO2,
            "SO2": i.SO2,
        })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": l})


# todo get_1_4
@app.route("/get_1_4")
def get_1_4():
    l = []

    化学 = {
        "SiO2": 0,
        "Na2O": 0,
        "K2O": 0,
        "CaO": 0,
        "MgO": 0,
        "Al2O3": 0,
        "Fe2O3": 0,
        "CuO": 0,
        "PbO": 0,
        "BaO": 0,
        "P2O5": 0,
        "SrO": 0,
        "SnO2": 0,
        "SO2": 0
    }
    type = {
        '高钾_非0': 化学.copy(),
        '高钾_0': 化学.copy(),
        '铅钡_非0': 化学.copy(),
        '铅钡_0': 化学.copy()
    }

    for i in db.session.query(C2).all():
        c1 = db.session.query(C1).filter(C1.cultural_relic_number == i.cultural_relics_sampling_point[0:2]).first()
        if c1.type == "高钾":
            if float(i.SiO2) != 0.0:
                type['高钾_非0']["SiO2"] += 1
            else:
                type['高钾_0']["SiO2"] += 1
            if float(i.Na2O) != 0.0:
                type['高钾_非0']["Na2O"] += 1
            else:
                type['高钾_0']["Na2O"] += 1
            if float(i.K2O) != 0.0:
                type['高钾_非0']["K2O"] += 1
            else:
                type['高钾_0']["K2O"] += 1
            if float(i.CaO) != 0.0:
                type['高钾_非0']["CaO"] += 1
            else:
                type['高钾_0']["CaO"] += 1
            if float(i.MgO) != 0.0:
                type['高钾_非0']["MgO"] += 1
            else:
                type['高钾_0']["MgO"] += 1
            if float(i.Al2O3) != 0.0:
                type['高钾_非0']["Al2O3"] += 1
            else:
                type['高钾_0']["Al2O3"] += 1
            if float(i.Fe2O3) != 0.0:
                type['高钾_非0']["Fe2O3"] += 1
            else:
                type['高钾_0']["Fe2O3"] += 1
            if float(i.CuO) != 0.0:
                type['高钾_非0']["CuO"] += 1
            else:
                type['高钾_0']["CuO"] += 1
            if float(i.PbO) != 0.0:
                type['高钾_非0']["PbO"] += 1
            else:
                type['高钾_0']["PbO"] += 1
            if float(i.BaO) != 0.0:
                type['高钾_非0']["BaO"] += 1
            else:
                type['高钾_0']["BaO"] += 1
            if float(i.P2O5) != 0.0:
                type['高钾_非0']["P2O5"] += 1
            else:
                type['高钾_0']["P2O5"] += 1
            if float(i.SrO) != 0.0:
                type['高钾_非0']["SrO"] += 1
            else:
                type['高钾_0']["SrO"] += 1
            if float(i.SnO2) != 0.0:
                type['高钾_非0']["SnO2"] += 1
            else:
                type['高钾_0']["SnO2"] += 1
            if float(i.SO2) != 0.0:
                type['高钾_非0']["SO2"] += 1
            else:
                type['高钾_0']["SO2"] += 1
        else:
            if float(i.SiO2) != 0.0:
                type['铅钡_非0']["SiO2"] += 1
            else:
                type['铅钡_0']["SiO2"] += 1
            if float(i.Na2O) != 0.0:
                type['铅钡_非0']["Na2O"] += 1
            else:
                type['铅钡_0']["Na2O"] += 1
            if float(i.K2O) != 0.0:
                type['铅钡_非0']["K2O"] += 1
            else:
                type['铅钡_0']["K2O"] += 1
            if float(i.CaO) != 0.0:
                type['铅钡_非0']["CaO"] += 1
            else:
                type['铅钡_0']["CaO"] += 1
            if float(i.MgO) != 0.0:
                type['铅钡_非0']["MgO"] += 1
            else:
                type['铅钡_0']["MgO"] += 1
            if float(i.Al2O3) != 0.0:
                type['铅钡_非0']["Al2O3"] += 1
            else:
                type['铅钡_0']["Al2O3"] += 1
            if float(i.Fe2O3) != 0.0:
                type['铅钡_非0']["Fe2O3"] += 1
            else:
                type['铅钡_0']["Fe2O3"] += 1
            if float(i.CuO) != 0.0:
                type['铅钡_非0']["CuO"] += 1
            else:
                type['铅钡_0']["CuO"] += 1
            if float(i.PbO) != 0.0:
                type['铅钡_非0']["PbO"] += 1
            else:
                type['铅钡_0']["PbO"] += 1
            if float(i.BaO) != 0.0:
                type['铅钡_非0']["BaO"] += 1
            else:
                type['铅钡_0']["BaO"] += 1
            if float(i.P2O5) != 0.0:
                type['铅钡_非0']["P2O5"] += 1
            else:
                type['铅钡_0']["P2O5"] += 1
            if float(i.SrO) != 0.0:
                type['铅钡_非0']["SrO"] += 1
            else:
                type['铅钡_0']["SrO"] += 1
            if float(i.SnO2) != 0.0:
                type['铅钡_非0']["SnO2"] += 1
            else:
                type['铅钡_0']["SnO2"] += 1
            if float(i.SO2) != 0.0:
                type['铅钡_非0']["SO2"] += 1
            else:
                type['铅钡_0']["SO2"] += 1

    for i in type:
        l.append({
            "type": i,
            "SiO2": type[i]['SiO2'],
            "Na2O": type[i]['Na2O'],
            "K2O": type[i]['K2O'],
            "CaO": type[i]['CaO'],
            "MgO": type[i]['MgO'],
            "Al2O3": type[i]['Al2O3'],
            "Fe2O3": type[i]['Fe2O3'],
            "CuO": type[i]['CuO'],
            "PbO": type[i]['PbO'],
            "BaO": type[i]['BaO'],
            "P2O5": type[i]['P2O5'],
            "SrO": type[i]['SrO'],
            "SnO2": type[i]['SnO2'],
            "SO2": type[i]['SO2']
        })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": l})


# todo get_1_5
@app.route("/get_1_5")
def get_1_5():
    l = []

    for i in db.session.query(C2).all():
        c1 = db.session.query(C1).filter(C1.cultural_relic_number == i.cultural_relics_sampling_point[0:2]).first()

        xl = []
        for x in db.session.query(C2).all():
            if c1.cultural_relic_number == x.cultural_relics_sampling_point[0:2]:
                xl.append(x)
        if len(xl) < 2:
            continue

        l.append({
            "cultural_relic_number": c1.cultural_relic_number,
            "surface_weathering": c1.surface_weathering,
            "cultural_relics_sampling_point": i.cultural_relics_sampling_point,
            "SiO2": i.SiO2,
            "Na2O": i.Na2O,
            "K2O": i.K2O,
            "CaO": i.CaO,
            "MgO": i.MgO,
            "Al2O3": i.Al2O3,
            "Fe2O3": i.Fe2O3,
            "CuO": i.CuO,
            "PbO": i.PbO,
            "BaO": i.BaO,
            "P2O5": i.P2O5,
            "SrO": i.SrO,
            "SnO2": i.SnO2,
            "SO2": i.SO2,
        })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": l})


# todo get_1_6
@app.route("/get_1_6")
def get_1_6():
    l = []

    for i in db.session.query(C2).all():
        c1 = db.session.query(C1).filter(C1.cultural_relic_number == i.cultural_relics_sampling_point[0:2]).first()

        if "严重" not in i.cultural_relics_sampling_point:
            continue

        l.append({
            "cultural_relic_number": c1.cultural_relic_number,
            "surface_weathering": c1.surface_weathering,
            "cultural_relics_sampling_point": i.cultural_relics_sampling_point,
            "SiO2": i.SiO2,
            "Na2O": i.Na2O,
            "K2O": i.K2O,
            "CaO": i.CaO,
            "MgO": i.MgO,
            "Al2O3": i.Al2O3,
            "Fe2O3": i.Fe2O3,
            "CuO": i.CuO,
            "PbO": i.PbO,
            "BaO": i.BaO,
            "P2O5": i.P2O5,
            "SrO": i.SrO,
            "SnO2": i.SnO2,
            "SO2": i.SO2,
        })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": l})


# todo get_2
@app.route("/get_2")
def get_2():
    l = []
    for i in db.session.query(C2).all():
        l.append({
            "cultural_relics_sampling_point": i.cultural_relics_sampling_point,
            "SiO2": i.SiO2,
            "Na2O": i.Na2O,
            "K2O": i.K2O,
            "CaO": i.CaO,
            "MgO": i.MgO,
            "Al2O3": i.Al2O3,
            "Fe2O3": i.Fe2O3,
            "CuO": i.CuO,
            "PbO": i.PbO,
            "BaO": i.BaO,
            "P2O5": i.P2O5,
            "SrO": i.SrO,
            "SnO2": i.SnO2,
            "SO2": i.SO2,
        })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": l})


# todo get_2_1
@app.route("/get_2_1")
def get_2_1():
    l = []
    for i in db.session.query(C2).all():
        l.append({
            "cultural_relics_sampling_point": i.cultural_relics_sampling_point,
            "SiO2": i.SiO2,
            "Na2O": i.Na2O,
            "K2O": i.K2O,
            "CaO": i.CaO,
            "MgO": i.MgO,
            "Al2O3": i.Al2O3,
            "Fe2O3": i.Fe2O3,
            "CuO": i.CuO,
            "PbO": i.PbO,
            "BaO": i.BaO,
            "P2O5": i.P2O5,
            "SrO": i.SrO,
            "SnO2": i.SnO2,
            "SO2": i.SO2,
            "sum": float(i.SiO2) + float(i.Na2O) + float(i.K2O) + float(i.CaO) + float(i.MgO) + float(i.Al2O3) + float(
                i.Fe2O3) + float(i.CuO) + float(i.PbO) + float(i.BaO) + float(i.P2O5) + float(i.SrO) + float(
                i.SnO2) + float(i.SO2)
        })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": l})


# todo get_2_2
@app.route("/get_2_2")
def get_2_2():
    l = []
    for i in db.session.query(C2).all():
        sum = float(i.SiO2) + float(i.Na2O) + float(i.K2O) + float(i.CaO) + float(i.MgO) + float(i.Al2O3) + float(
            i.Fe2O3) + float(i.CuO) + float(i.PbO) + float(i.BaO) + float(i.P2O5) + float(i.SrO) + float(
            i.SnO2) + float(i.SO2)
        if sum < 85 or sum > 105:
            continue

        l.append({
            "cultural_relics_sampling_point": i.cultural_relics_sampling_point,
            "SiO2": i.SiO2,
            "Na2O": i.Na2O,
            "K2O": i.K2O,
            "CaO": i.CaO,
            "MgO": i.MgO,
            "Al2O3": i.Al2O3,
            "Fe2O3": i.Fe2O3,
            "CuO": i.CuO,
            "PbO": i.PbO,
            "BaO": i.BaO,
            "P2O5": i.P2O5,
            "SrO": i.SrO,
            "SnO2": i.SnO2,
            "SO2": i.SO2,
            "sum": sum
        })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": l})


# todo get_2_3
@app.route("/get_2_3")
def get_2_3():
    l = []
    for i in db.session.query(C2).all():
        sum = float(i.SiO2) + float(i.Na2O) + float(i.K2O) + float(i.CaO) + float(i.MgO) + float(i.Al2O3) + float(
            i.Fe2O3) + float(i.CuO) + float(i.PbO) + float(i.BaO) + float(i.P2O5) + float(i.SrO) + float(
            i.SnO2) + float(i.SO2)
        if 85 < sum < 105:
            continue

        l.append({
            "cultural_relics_sampling_point": i.cultural_relics_sampling_point,
            "SiO2": i.SiO2,
            "Na2O": i.Na2O,
            "K2O": i.K2O,
            "CaO": i.CaO,
            "MgO": i.MgO,
            "Al2O3": i.Al2O3,
            "Fe2O3": i.Fe2O3,
            "CuO": i.CuO,
            "PbO": i.PbO,
            "BaO": i.BaO,
            "P2O5": i.P2O5,
            "SrO": i.SrO,
            "SnO2": i.SnO2,
            "SO2": i.SO2,
            "sum": sum
        })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": l})


# todo get_3
@app.route("/get_3")
def get_3():
    l = []
    for i in db.session.query(C3).all():
        l.append({
            "cultural_relic_number": i.cultural_relic_number,
            "surface_weathering": i.surface_weathering,
            "SiO2": i.SiO2,
            "Na2O": i.Na2O,
            "K2O": i.K2O,
            "CaO": i.CaO,
            "MgO": i.MgO,
            "Al2O3": i.Al2O3,
            "Fe2O3": i.Fe2O3,
            "CuO": i.CuO,
            "PbO": i.PbO,
            "BaO": i.BaO,
            "P2O5": i.P2O5,
            "SrO": i.SrO,
            "SnO2": i.SnO2,
            "SO2": i.SO2
        })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": l})


# todo DBI 戴维森堡丁指数
@app.route("/get_dbi")
def get_dbi():
    col = ['SiO2', 'Na2O', 'K2O', 'CaO', 'MgO', 'Al2O3', 'Fe2O3', 'CuO', 'PbO', 'BaO', 'P2O5', 'SrO', 'SnO2', 'SO2']
    DaviesBouldin_list = []
    target = []
    res_s = []
    for i in db.session.query(C2).all():
        DaviesBouldin_list.append([float(vars(i)[element]) for element in col])
        target.append(0 if i.c1.type == "高钾" else 1)

    DaviesBouldin_list = np.array(DaviesBouldin_list)
    target = np.array(target)

    res = DaviesBouldin(DaviesBouldin_list, target)
    res_s.append(res)

    return str(res)


# todo 多元线性回归模型: (风化/无风化) 与 各种化学元素间的关系
@app.route("/grt_regression")
def grt_regression():
    col = ['SiO2', 'Na2O', 'K2O', 'CaO', 'MgO', 'Al2O3', 'Fe2O3', 'CuO', 'PbO', 'BaO', 'P2O5', 'SrO', 'SnO2', 'SO2']
    target = []
    l = []
    for i in db.session.query(C2).all():
        c1 = db.session.query(C1).filter(C1.cultural_relic_number == i.cultural_relics_sampling_point[0:2]).first()
        l.append([
            float(i.SiO2),
            float(i.Na2O),
            float(i.K2O),
            float(i.CaO),
            float(i.MgO),
            float(i.Al2O3),
            float(i.Fe2O3),
            float(i.CuO),
            float(i.PbO),
            float(i.BaO),
            float(i.P2O5),
            float(i.SrO),
            float(i.SnO2),
            float(i.SO2)
        ])
        target.append(1 if c1.surface_weathering == "风化" else 0)
        # target.append(float(i.SiO2))
    bostondf = pd.DataFrame(l, columns=col)
    bostondf['medv'] = target
    bostondf.head()

    # 多元回归分析
    mod = smf.ols(formula='medv~SiO2+Na2O+K2O+CaO+MgO+Al2O3+Fe2O3+CuO+PbO+BaO+P2O5+SrO+SnO2+SO2', data=bostondf)
    res = mod.fit()

    response_l = []
    for i in res.summary().tables[1].data:
        response_l.append({
            "0": i[0],
            "1": i[1],
            "2": i[2],
            "3": i[3],
            "4": i[4],
            "5": i[5],
            "6": i[6],
        })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": response_l})


# todo 多元线性回归模型 多元素相互关系
@app.route("/grt_regression_1")
def grt_regression_1():
    col = ['SiO2', 'Na2O', 'K2O', 'CaO', 'MgO', 'Al2O3', 'Fe2O3', 'CuO', 'PbO', 'BaO', 'P2O5', 'SrO', 'SnO2', 'SO2']
    target = []
    l = []

    response_l = []
    for element in col:
        col2 = col[:]
        col2.remove(element)
        for i in db.session.query(C2).all():
            c1 = db.session.query(C1).filter(C1.cultural_relic_number == i.cultural_relics_sampling_point[0:2]).first()
            data = {
                "SiO2": float(i.SiO2),
                "Na2O": float(i.Na2O),
                "K2O": float(i.K2O),
                "CaO": float(i.CaO),
                'MgO': float(i.MgO),
                'Al2O3': float(i.Al2O3),
                'Fe2O3': float(i.Fe2O3),
                'CuO': float(i.CuO),
                'PbO': float(i.PbO),
                'BaO': float(i.BaO),
                'P2O5': float(i.P2O5),
                'SrO': float(i.SrO),
                'SnO2': float(i.SnO2),
                'SO2': float(i.SO2)
            }
            l.append([])
            for element_col2 in col2:
                l[-1].append(data[element_col2])
            target.append(float(i.SiO2))
        bostondf = pd.DataFrame(l, columns=col2)
        bostondf['medv'] = target
        bostondf.head()

        medv_str = "medv~"
        for i in col2:
            medv_str += i
            medv_str += "+"
        # 多元回归分析
        mod = smf.ols(formula=medv_str[:-1], data=bostondf)
        res = mod.fit()

        print(element)
        response_l.append({"0": '', "1": '', "2": '', "3": element, "4": "", "5": '', "6": '', })
        for i in res.summary().tables[1].data:
            response_l.append({
                "0": i[0],
                "1": i[1],
                "2": i[2],
                "3": i[3],
                "4": i[4],
                "5": i[5],
                "6": i[6],
            })
        response_l.append({"0": '', "1": '', "2": '', "3": '', "4": "", "5": '', "6": '', })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": response_l})


# todo 多元线性回归模型 多元素相互关系 高钾
@app.route("/grt_regression_2")
def grt_regression_2():
    col = ['SiO2', 'Na2O', 'K2O', 'CaO', 'MgO', 'Al2O3', 'Fe2O3', 'CuO', 'PbO', 'BaO', 'P2O5', 'SrO', 'SnO2', 'SO2']
    target = []
    l = []

    response_l = []
    for element in col:
        col2 = col[:]
        col2.remove(element)
        for i in db.session.query(C2).all():
            c1 = db.session.query(C1).filter(C1.cultural_relic_number == i.cultural_relics_sampling_point[0:2]).first()
            if c1.type != "高钾":
                continue

            data = {
                "SiO2": float(i.SiO2),
                "Na2O": float(i.Na2O),
                "K2O": float(i.K2O),
                "CaO": float(i.CaO),
                'MgO': float(i.MgO),
                'Al2O3': float(i.Al2O3),
                'Fe2O3': float(i.Fe2O3),
                'CuO': float(i.CuO),
                'PbO': float(i.PbO),
                'BaO': float(i.BaO),
                'P2O5': float(i.P2O5),
                'SrO': float(i.SrO),
                'SnO2': float(i.SnO2),
                'SO2': float(i.SO2)
            }
            l.append([])
            for element_col2 in col2:
                l[-1].append(data[element_col2])
            target.append(float(i.SiO2))
        bostondf = pd.DataFrame(l, columns=col2)
        bostondf['medv'] = target
        bostondf.head()

        medv_str = "medv~"
        for i in col2:
            medv_str += i
            medv_str += "+"
        # 多元回归分析
        mod = smf.ols(formula=medv_str[:-1], data=bostondf)
        res = mod.fit()

        print(element)
        response_l.append({"0": '', "1": '', "2": '', "3": element, "4": "", "5": '', "6": '', })
        for i in res.summary().tables[1].data:
            response_l.append({
                "0": i[0],
                "1": i[1],
                "2": i[2],
                "3": i[3],
                "4": i[4],
                "5": i[5],
                "6": i[6],
            })
        response_l.append({"0": '', "1": '', "2": '', "3": '', "4": "", "5": '', "6": '', })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": response_l})


# todo 多元线性回归模型 多元素相互关系 铅钡
@app.route("/grt_regression_3")
def grt_regression_3():
    col = ['SiO2', 'Na2O', 'K2O', 'CaO', 'MgO', 'Al2O3', 'Fe2O3', 'CuO', 'PbO', 'BaO', 'P2O5', 'SrO', 'SnO2', 'SO2']
    target = []
    l = []

    response_l = []
    for element in col:
        col2 = col[:]
        col2.remove(element)
        for i in db.session.query(C2).all():
            c1 = db.session.query(C1).filter(C1.cultural_relic_number == i.cultural_relics_sampling_point[0:2]).first()
            if c1.type != "铅钡":
                continue

            data = {
                "SiO2": float(i.SiO2),
                "Na2O": float(i.Na2O),
                "K2O": float(i.K2O),
                "CaO": float(i.CaO),
                'MgO': float(i.MgO),
                'Al2O3': float(i.Al2O3),
                'Fe2O3': float(i.Fe2O3),
                'CuO': float(i.CuO),
                'PbO': float(i.PbO),
                'BaO': float(i.BaO),
                'P2O5': float(i.P2O5),
                'SrO': float(i.SrO),
                'SnO2': float(i.SnO2),
                'SO2': float(i.SO2)
            }
            l.append([])
            for element_col2 in col2:
                l[-1].append(data[element_col2])
            target.append(float(i.SiO2))
        bostondf = pd.DataFrame(l, columns=col2)
        bostondf['medv'] = target
        bostondf.head()

        medv_str = "medv~"
        for i in col2:
            medv_str += i
            medv_str += "+"
        # 多元回归分析
        mod = smf.ols(formula=medv_str[:-1], data=bostondf)
        res = mod.fit()

        print(element)
        response_l.append({"0": '', "1": '', "2": '', "3": element, "4": "", "5": '', "6": '', })
        for i in res.summary().tables[1].data:
            response_l.append({
                "0": i[0],
                "1": i[1],
                "2": i[2],
                "3": i[3],
                "4": i[4],
                "5": i[5],
                "6": i[6],
            })
        response_l.append({"0": '', "1": '', "2": '', "3": '', "4": "", "5": '', "6": '', })

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": response_l})


# todo 决策树 第三题 预测类别
@app.route("/get_tree")
def get_tree():
    # feature_names = ['SiO2', 'Na2O', 'K2O', 'CaO', 'MgO', 'Al2O3', 'Fe2O3', 'CuO', 'PbO', 'BaO', 'P2O5', 'SrO', 'SnO2', 'SO2']
    feature_names = ['SiO2', 'PbO']

    c3_result = []

    # ---------------- 风化的情况 ----------------------------------------------------------
    target = []
    l = []
    for i in db.session.query(C2_2).all():
        if i.c1_1.surface_weathering != "风化":
            continue

        l.append([
            float(i.SiO2),
            # float(i.Na2O),
            # float(i.K2O),
            # float(i.CaO),
            # float(i.MgO),
            # float(i.Al2O3),
            # float(i.Fe2O3),
            # float(i.CuO),
            float(i.PbO),
            # float(i.BaO),
            # float(i.P2O5),
            # float(i.SrO),
            # float(i.SnO2),
            # float(i.SO2)
        ])
        target.append(0 if i.c1_1.type == "高钾" else 1)
    l = np.array(l)
    target = np.array(target)
    x_train, x_test, y_train, y_test = train_test_split(l, target, test_size=0.5)
    # 决策树不需要标准化/归一化
    estimator = DecisionTreeClassifier(criterion="entropy", max_leaf_nodes=12)
    estimator.fit(x_train, y_train)
    # 5.模型评估
    all_l = []
    c3 = db.session.query(C3).all()
    for i in c3:
        all_l.append([
            float(i.SiO2),
            # float(i.Na2O),
            # float(i.K2O),
            # float(i.CaO),
            # float(i.MgO),
            # float(i.Al2O3),
            # float(i.Fe2O3),
            # float(i.CuO),
            float(i.PbO),
            # float(i.BaO),
            # float(i.P2O5),
            # float(i.SrO),
            # float(i.SnO2),
            # float(i.SO2)
        ])

    # y_predict = estimator.predict(x_test)
    y_predict = estimator.predict(all_l)  # 预测第三问的数据
    # print(y_predict)
    count_1 = 0
    for i in c3:

        c3_result.append({
            'cultural_relic_number': i.cultural_relic_number,
            'type': "高钾" if y_predict[count_1] == 0 else "铅钡"
        })
        count_1 += 1

    # 可视化
    # http://dreampuf.github.io/GraphvizOnline/
    export_graphviz(estimator, out_file="tree.dot", feature_names=feature_names)

    return jsonify({"code": 0, "msg": "", "count": 1000, "data": c3_result})


# todo 决策树 第三题 敏感性分析 模型评估 循环 100 次
@app.route("/get_tree_2")
def get_tree_2():
    feature_names = ['SiO2', 'Na2O', 'K2O', 'CaO', 'MgO', 'Al2O3', 'Fe2O3', 'CuO', 'PbO', 'BaO', 'P2O5', 'SrO', 'SnO2',
                     'SO2']
    # feature_names = ['Na2O', 'CaO', 'MgO', 'Al2O3', 'Fe2O3', 'CuO', 'P2O5', 'SnO2', 'SO2']

    return_data = []

    count = 0
    for i in range(1):
        count += 1
        target = []
        l = []
        print("风化")
        for i in db.session.query(C2_2).all():
            if not i.c1_1.surface_weathering == '风化':
                continue

            l.append([
                float(i.SiO2),
                float(i.Na2O),
                float(i.K2O),
                float(i.CaO),
                float(i.MgO),
                float(i.Al2O3),
                float(i.Fe2O3),
                float(i.CuO),
                float(i.PbO),
                float(i.BaO),
                float(i.P2O5),
                float(i.SrO),
                float(i.SnO2),
                float(i.SO2)
            ])
            target.append(0 if i.c1_1.type == "高钾" else 1)
        l = np.array(l)
        target = np.array(target)
        x_train, x_test, y_train, y_test = train_test_split(l, target, test_size=0.5)
        # 决策树不需要标准化/归一化
        estimator = DecisionTreeClassifier(criterion="entropy", max_leaf_nodes=12)
        estimator.fit(x_train, y_train)
        # 5.模型评估
        y_predict = estimator.predict(x_test)
        # print(y_predict)

        print("y_predict:\n", y_predict)
        print("y_test:\n", y_test)
        print("直接比真实值和预测值：\n", y_test == y_predict)
        score = estimator.score(x_test, y_test)
        print("准确率为：\n", score)
        return_data.append({
            "y_predict": list(map(int, y_predict)),
            "count": count,
            "score": score
        })

    # 可视化
    # http://dreampuf.github.io/GraphvizOnline/
    export_graphviz(estimator, out_file="tree.dot", feature_names=feature_names)

    return jsonify(return_data)


# todo url index
@app.route("/")
def index():
    return render_template("index.html")


# todo url sheet_1
@app.route("/sheet_1")
def sheet_1():
    return render_template("sheet_1.html")


# todo url sheet_1_1
@app.route("/sheet_1_1")
def sheet_1_1():
    return render_template("sheet_1_1.html")


# todo url sheet_1_2
@app.route("/sheet_1_2")
def sheet_1_2():
    return render_template("sheet_1_2.html")


# todo url sheet_1_2_0
@app.route("/sheet_1_2_0")
def sheet_1_2_0():
    return render_template("sheet_1_2_0.html")


# todo url sheet_1_2_1
@app.route("/sheet_1_2_1")
def sheet_1_2_1():
    return render_template("sheet_1_2_1.html")


# todo url sheet_1_2_2
@app.route("/sheet_1_2_2")
def sheet_1_2_2():
    return render_template("sheet_1_2_2.html")


# todo url sheet_1_2_2_1
@app.route("/sheet_1_2_2_1")
def sheet_1_2_2_1():
    return render_template("sheet_1_2_2_1.html")


# todo url sheet_1_2_2_2
@app.route("/sheet_1_2_2_2")
def sheet_1_2_2_2():
    return render_template("sheet_1_2_2_2.html")


# todo url sheet_1_2_2_3
@app.route("/sheet_1_2_2_3")
def sheet_1_2_2_3():
    return render_template("sheet_1_2_2_3.html")


# todo url sheet_1_2_2_4
@app.route("/sheet_1_2_2_4")
def sheet_1_2_2_4():
    return render_template("sheet_1_2_2_4.html")


# todo url sheet_1_3
@app.route("/sheet_1_3")
def sheet_1_3():
    return render_template("sheet_1_3.html")


# todo url sheet_1_4
@app.route("/sheet_1_4")
def sheet_1_4():
    return render_template("sheet_1_4.html")


# todo url sheet_1_5
@app.route("/sheet_1_5")
def sheet_1_5():
    return render_template("sheet_1_5.html")


# todo url sheet_1_6
@app.route("/sheet_1_6")
def sheet_1_6():
    return render_template("sheet_1_6.html")


# todo url sheet_2
@app.route("/sheet_2")
def sheet_2():
    return render_template("sheet_2.html")


# todo url sheet_2_1
@app.route("/sheet_2_1")
def sheet_2_1():
    return render_template("sheet_2_1.html")


# todo url sheet_2_2
@app.route("/sheet_2_2")
def sheet_2_2():
    return render_template("sheet_2_2.html")


# todo url sheet_2_3
@app.route("/sheet_2_3")
def sheet_2_3():
    return render_template("sheet_2_3.html")


# todo url sheet_2_4
@app.route("/sheet_2_4")
def sheet_2_4():
    return render_template("sheet_2_4.html")


# todo url sheet_3
@app.route("/sheet_3")
def sheet_3():
    return render_template("sheet_3.html")


# todo url sheet_3_1
@app.route("/sheet_3_1")
def sheet_3_1():
    return render_template("sheet_3_1.html")


# todo url echarts_1
@app.route("/echarts_1")
def echarts_1():
    content = {
        'data': [
            [10.0, 8.04],
            [8.07, 6.95],
            [13.0, 7.58],
            [9.05, 8.81],
            [11.0, 8.33],
            [14.0, 7.66],
            [13.4, 6.81],
            [10.0, 6.33],
            [14.0, 8.96],
            [12.5, 6.82],
            [9.15, 7.2],
            [11.5, 7.2],
            [3.03, 4.23],
            [12.2, 7.83],
            [2.02, 4.47],
            [1.05, 3.33],
            [4.05, 4.96],
            [6.03, 7.24],
            [12.0, 6.26],
            [12.0, 8.84],
            [7.08, 5.82],
            [5.02, 5.68]
        ]
    }
    return render_template("echarts_1.html", **content)


# todo url echarts_2
@app.route("/echarts_2")
def echarts_2():
    # 数据路径
    path = "static/json/sheet_1_4.json"
    # 读取文件数据
    with open(path, "r") as f:
        row_data = json.load(f)
    data = row_data["data"]
    print(data)
    # response = requests.get("/get_1_4", verify=False, stream=True)
    # print(response.status_code)
    # print(response.text)

    series_data = [[], [], [], []]
    for i in range(len(data)):
        for key in data[i]:
            series_data[i].append(data[i][key])

    # print(series_data)

    content = {
        "K_NOT_0_series_data": series_data[0][:-1],
        "K_0_series_data": series_data[1][:-1],
        "Pb_NOT_0_series_data": series_data[2][:-1],
        "Pb_0_series_data": series_data[3][:-1]
    }
    return render_template("echarts_2.html", **content)


# todo url echarts_3
@app.route("/echarts_3")
def echarts_3():
    # 数据路径
    path = "static/json/get_1_6.json"
    # 读取文件数据
    with open(path, "r") as f:
        row_data = json.load(f)
    series_data = [[], [], []]
    for i in range(len(row_data["data"])):
        for j in row_data["data"][i]:
            series_data[i].append(row_data["data"][i][j])

    print(series_data)

    content = {
        "series_data_08": list(map(float, series_data[0][:-2])),
        "series_data_26": list(map(float, series_data[1][:-2])),
        "series_data_54": list(map(float, series_data[2][:-2]))
    }
    return render_template("echarts_3.html", **content)


def echarts_4_fun(data):
    return float("{:.2f}".format(float(data)))


# todo url echarts_4
@app.route("/echarts_4")
def echarts_4():
    with open("static/json/get_1_3.json", "r") as f:
        row_data = json.load(f)
    data = row_data["data"]

    dict_name = ['SiO2', 'Na2O', 'K2O', 'CaO', 'MgO', 'Al2O3', 'Fe2O3', 'CuO', 'PbO', 'BaO', 'P2O5', 'SrO', 'SnO2',
                 'SO2']
    no_wind = []
    wind = []
    wind_hard = []
    for i in data:
        if i['type'] != '铅钡':
            continue

        if i["surface_weathering"] == "无风化":
            l_1 = []
            for j in dict_name:
                l_1.append(float(i[j]))
            no_wind.append(l_1)
        elif i["surface_weathering"] == "风化":
            l_1 = []
            for j in dict_name:
                l_1.append(float(i[j]))
            wind.append(l_1)
        else:
            l_1 = []
            for j in dict_name:
                l_1.append(float(i[j]))
            wind_hard.append(l_1)

    # print(np.array(no_wind))
    no_wind_average = list(map(echarts_4_fun, np.average(np.array(no_wind), axis=0)))  # 按列求均值
    wind_average = list(map(echarts_4_fun, np.average(np.array(wind), axis=0)))  # 按列求均值
    wind_hard_average = list(map(echarts_4_fun, np.average(np.array(wind_hard), axis=0)))  # 按列求均值
    print(no_wind_average)
    print(wind_average)
    print(wind_hard_average)
    content = {
        "no_wind_average": no_wind_average,
        "wind_average": wind_average,
        "wind_hard_average": wind_hard_average
    }
    return render_template("echarts_4.html", **content)


# todo url echarts_5
@app.route("/echarts_5")
def echarts_5():
    with open("static/json/tree_score_100.json", "r") as f:
        row_data = json.load(f)

    series_data = []
    for i in row_data:
        series_data.append(i["score"])

    content = {
        "series_data": series_data,
        "index_list": [i for i in range(len(series_data))]
    }
    return render_template("echarts_5.html", **content)


# todo url echarts_6 高钾 无风化
@app.route("/echarts_6")
def echarts_6():
    elements = ['SiO2', 'Na2O', 'K2O', 'CaO', 'MgO', 'Al2O3', 'Fe2O3', 'CuO', 'PbO', 'BaO', 'P2O5', 'SrO', 'SnO2',
                'SO2']

    series = []
    xAxis_data = []
    append_data = {}

    for i in db.session.query(C2):
        if not (i.c1.type == "高钾" and i.c1.surface_weathering == "无风化"):
            continue

        xAxis_data.append(i.cultural_relics_sampling_point)

        for element in elements:
            if element not in append_data:
                append_data[element] = {
                    'name': element,
                    'type': 'bar',
                    'barGap': 0,
                    'emphasis': {
                        'focus': 'series'
                    },
                    'data': []
                }

            append_data[element]["data"].append(vars(i)[element])

    # print(append_data)
    for i in append_data:
        series.append(append_data[i])

    content = {
        "table_name": "高钾 无风化",
        'series': series,
        "xAxis_data": xAxis_data,
        "element": elements
    }
    return render_template("echarts_6.html", **content)


# todo url echarts_7 铅钡 无风化
@app.route("/echarts_7")
def echarts_7():
    elements = ['SiO2', 'Na2O', 'K2O', 'CaO', 'MgO', 'Al2O3', 'Fe2O3', 'CuO', 'PbO', 'BaO', 'P2O5', 'SrO', 'SnO2',
                'SO2']

    series = []
    xAxis_data = []
    append_data = {}

    for i in db.session.query(C2):
        if not (i.c1.type == "铅钡" and i.c1.surface_weathering == "无风化"):
            continue

        xAxis_data.append(i.cultural_relics_sampling_point)

        for element in elements:
            if element not in append_data:
                append_data[element] = {
                    'name': element,
                    'type': 'bar',
                    'barGap': 0,
                    'emphasis': {
                        'focus': 'series'
                    },
                    'data': []
                }

            append_data[element]["data"].append(vars(i)[element])

    # print(append_data)
    for i in append_data:
        series.append(append_data[i])

    content = {
        "table_name": "铅钡 无风化",
        'series': series,
        "xAxis_data": xAxis_data,
        "element": elements
    }
    return render_template("echarts_7.html", **content)


# todo url echarts_8
@app.route("/echarts_8")
def echarts_8():
    elements = ['SiO2', 'Na2O', 'K2O', 'CaO', 'MgO', 'Al2O3', 'Fe2O3', 'CuO', 'PbO', 'BaO', 'P2O5', 'SrO', 'SnO2',
                'SO2']

    series = []
    xAxis_data = []
    append_data = {}

    for i in db.session.query(C2):
        if not (i.c1.type == "高钾" and i.c1.surface_weathering == "风化"):
            continue

        xAxis_data.append(i.cultural_relics_sampling_point)

        for element in elements:
            if element not in append_data:
                append_data[element] = {
                    'name': element,
                    'type': 'bar',
                    'barGap': 0,
                    'emphasis': {
                        'focus': 'series'
                    },
                    'data': []
                }

            append_data[element]["data"].append(vars(i)[element])

    # print(append_data)
    for i in append_data:
        series.append(append_data[i])

    content = {
        "table_name": "高钾 风化",
        'series': series,
        "xAxis_data": xAxis_data,
        "element": elements
    }
    return render_template("echarts_8.html", **content)


# todo url echarts_9
@app.route("/echarts_9")
def echarts_9():
    elements = ['SiO2', 'Na2O', 'K2O', 'CaO', 'MgO', 'Al2O3', 'Fe2O3', 'CuO', 'PbO', 'BaO', 'P2O5', 'SrO', 'SnO2',
                'SO2']

    series = []
    xAxis_data = []
    append_data = {}

    for i in db.session.query(C2):
        if not (i.c1.type == "铅钡" and i.c1.surface_weathering == "风化"):
            continue

        xAxis_data.append(i.cultural_relics_sampling_point)

        for element in elements:
            if element not in append_data:
                append_data[element] = {
                    'name': element,
                    'type': 'bar',
                    'barGap': 0,
                    'emphasis': {
                        'focus': 'series'
                    },
                    'data': []
                }

            append_data[element]["data"].append(vars(i)[element])

    # print(append_data)
    for i in append_data:
        series.append(append_data[i])

    content = {
        "table_name": "铅钡 风化",
        'series': series,
        "xAxis_data": xAxis_data,
        "element": elements
    }
    return render_template("echarts_9.html", **content)


# todo url echarts_10
@app.route("/echarts_10")
def echarts_10():
    elements = ['SiO2', 'K2O', 'CaO', 'Al2O3']

    series = []
    xAxis_data = []
    append_data = {}

    for i in db.session.query(C2):
        if not (i.c1.type == "高钾" and i.c1.surface_weathering == "无风化"):
            continue

        xAxis_data.append(i.cultural_relics_sampling_point)

        for element in elements:
            if element not in append_data:
                append_data[element] = {
                    'name': element,
                    'type': 'bar',
                    'barGap': 0,
                    'emphasis': {
                        'focus': 'series'
                    },
                    'data': []
                }

            append_data[element]["data"].append(vars(i)[element])

    for i in append_data:
        series.append(append_data[i])

    content = {
        "table_name": "高钾 无风化",
        'series': series,
        "xAxis_data": xAxis_data,
        "element": elements
    }
    return render_template("echarts_10.html", **content)


# todo url echarts_11 铅钡 无风化
@app.route("/echarts_11")
def echarts_11():
    elements = ['SiO2', 'PbO']

    series = []
    xAxis_data = []
    append_data = {}

    for i in db.session.query(C2):
        if not (i.c1.type == "铅钡" and i.c1.surface_weathering == "无风化"):
            continue

        xAxis_data.append(i.cultural_relics_sampling_point)

        for element in elements:
            if element not in append_data:
                append_data[element] = {
                    'name': element,
                    'type': 'bar',
                    'barGap': 0,
                    'emphasis': {
                        'focus': 'series'
                    },
                    'data': []
                }

            append_data[element]["data"].append(vars(i)[element])

    # print(append_data)
    for i in append_data:
        series.append(append_data[i])

    content = {
        "table_name": "铅钡 无风化",
        'series': series,
        "xAxis_data": xAxis_data,
        "element": elements
    }
    return render_template("echarts_11.html", **content)


# todo url echarts_12
@app.route("/echarts_12")
def echarts_12():
    elements = ['SiO2', 'K2O', 'CaO', 'Al2O3']

    series = []
    xAxis_data = []
    append_data = {}

    for i in db.session.query(C2):
        if not (i.c1.type == "高钾" and i.c1.surface_weathering == "风化"):
            continue

        xAxis_data.append(i.cultural_relics_sampling_point)

        for element in elements:
            if element not in append_data:
                append_data[element] = {
                    'name': element,
                    'type': 'bar',
                    'barGap': 0,
                    'emphasis': {
                        'focus': 'series'
                    },
                    'data': []
                }

            append_data[element]["data"].append(vars(i)[element])

    # print(append_data)
    for i in append_data:
        series.append(append_data[i])

    content = {
        "table_name": "高钾 风化",
        'series': series,
        "xAxis_data": xAxis_data,
        "element": elements
    }
    return render_template("echarts_12.html", **content)


# todo url echarts_13
@app.route("/echarts_13")
def echarts_13():
    elements = ['SiO2', 'PbO']

    series = []
    xAxis_data = []
    append_data = {}

    for i in db.session.query(C2):
        if not (i.c1.type == "铅钡" and i.c1.surface_weathering == "风化"):
            continue

        xAxis_data.append(i.cultural_relics_sampling_point)

        for element in elements:
            if element not in append_data:
                append_data[element] = {
                    'name': element,
                    'type': 'bar',
                    'barGap': 0,
                    'emphasis': {
                        'focus': 'series'
                    },
                    'data': []
                }

            append_data[element]["data"].append(vars(i)[element])

    # print(append_data)
    for i in append_data:
        series.append(append_data[i])

    content = {
        "table_name": "铅钡 风化",
        'series': series,
        "xAxis_data": xAxis_data,
        "element": elements
    }
    return render_template("echarts_13.html", **content)


# todo url regression
@app.route("/regression")
def regression():
    return render_template("regression.html")


# todo url regression_1
@app.route("/regression_1")
def regression_1():
    return render_template("regression_1.html")


# todo url regression_2
@app.route("/regression_2")
def regression_2():
    return render_template("regression_2.html")


# todo url regression_3
@app.route("/regression_3")
def regression_3():
    return render_template("regression_3.html")


# todo url other_1
@app.route("/other_1")
def other_1():
    return render_template("other_1.html")


# todo mysql_create_1 c1 c2 '文物采样点' 替换 '表面风化'
@app.route("/mysql_create_1")
def mysql_create_1():
    return "error"

    for i in db.session.query(C2):
        c1_1 = C1_1(
            cultural_relic_number=i.c1.cultural_relic_number,
            ornamentation=i.c1.ornamentation,
            type=i.c1.type,
            color=i.c1.color,
            surface_weathering=i.c1.surface_weathering if len(
                i.cultural_relics_sampling_point) == 2 or "部位" in i.cultural_relics_sampling_point else get_1_3_fun(
                i.cultural_relics_sampling_point),
        )
        c2_2 = C2_2(
            cultural_relics_sampling_point=i.cultural_relics_sampling_point,
            SiO2=i.SiO2,
            Na2O=i.Na2O,
            K2O=i.K2O,
            CaO=i.CaO,
            MgO=i.MgO,
            Al2O3=i.Al2O3,
            Fe2O3=i.Fe2O3,
            CuO=i.CuO,
            PbO=i.PbO,
            BaO=i.BaO,
            P2O5=i.P2O5,
            SrO=i.SrO,
            SnO2=i.SnO2,
            SO2=i.SO2,
            c1_1=c1_1
        )
        db.session.add(c1_1)
        db.session.add(c2_2)

    db.session.commit()
    db.session.close()

    return "mysql_create_1"


if __name__ == '__main__':
    app.run()
